PhAST Estadísticas

Introducción

Históricamente, las operaciones desde terminales POS enfrentan dificultades relacionadas con la naturaleza del entorno, o la tecnología en sí; estas dificultades se pueden deducir analizando algunas comparaciones del estado circundante que dan lugar a la operación.

Las estadísticas son información crucial para evaluar la salud del sistema PhAST en su conjunto. El PhAST Client, en cada operación, registra información relacionada con la NSU local de la operación actual y luego la envía a PhAST Server para su consolidación y persistencia.

La información recopilada y recopilada de toda una red, permite una reflexión interesante que permite todo, desde ayudar a tomar decisiones comerciales hasta afinar el sistema.

Las operaciones de Keepalive no generan ni transfieren información estadística.

---

Persistencia x Transmisión

Hasta hace poco, las estadísticas siempre se enviaban sobre las operaciones (la operación actual llevaba las estadísticas de la última operación exitosa y más las siguientes que fallaron si existen).

Actualmente, el POS retiene estadísticas hasta que ocurren algunos eventos:

• Tiene lugar una transmisión / inicialización;
• La primera transacción se realiza después de que se enciende la terminal;
• La 1ª transacción tiene lugar después de 1 hora después del último envío de estadísticas;

Mientras el POS no envía estadísticas, los almacena en un repositorio aislado, adecuado para la persistencia de esta información. Esta tienda tiene un límite de 100 registros de estadísticas; una vez que se excede este límite, el POS simplemente deja de registrar más estadísticas.

Para evitar problemas por exceso de información, el envío de estadísticas está limitado a 20 registros. Es decir, si el POS tiene 99 estadísticas, solo se enviarán 20 por entrega.

Si la cantidad de estadísticas supera el límite (20) establecido para el envío, incluso si el último envío fue hace menos de 1 hora o si la operación actual es una transacción, las estadísticas se enviarán juntas. Esto evita que el POS funcione con una alta carga de estadísticas.

---

Definición CSTD

    TABLE STATISTICS {
        control     BYTE, // usage(2 bits) + reason (3 bits) + result (3 bits)<br>
        nsu         DWORD,
        timestamp   DATETIME,
        TABLE INFO {
            id      BYTE, // tipo + id
            ticks   WORD,
            info    VARIANT
        }
    }

---

Tipos

• START(0)
• FINISH(1)

---

Uso

• PHDM(0)
• INITIALIZATION(1)
• TRANSACTION(2)
• KEEPALIVE(3)

---

Razón

• NO IDENTIFICADO(0)
• OPERADOR(1)
• EVENTO(2)
• INTEGRACIÓN(3)
• SERVIDOR(4)
• ACTUALIZAR(5)

---

Resulta

• APPROVED_ONLINE(0)
• APPROVED_OFFLINE(1)
• DENIED_ONLINE(2)
• DENIED_OFFLINE(3)
• ERROR_COMM(4)
• ABORTED(5)

---

Información Estadística

Información Común

Errores

5 - comm (STRUCT):

• code (BYTE)
• detail (DWORD)
• native (SDWORD)

6 - operation (STRUCT):

• code (BYTE)
• message (STRING)

Informaciones

10 - status (STRUCT):

• battery (BYTE) – de 0 a 100, 99 (no disponible) bit más significativo indica si se está cargando)
• rssi (BYTE) – de 0 a 31, 99 (no disponible)
• ber (BYTE) – de 0 a 7, 99 (no disponible)
• geo (BLOB)

15 - conn (STRUCT)

• id (WORD)
• type (BYTE)
• 01 - ETHERNET
• 02 - Dial/TCP
• 03 - WIRELESS/GPRS
• 04 - DIAL
• 05 - SERIAL
• 06 - EPACK
• 07 - RENPAC
• 08 - WIRELESS_CSD
• 09 - WIRELESS_WIFI
• 10 - RENPAC_PPP
• 11 – SDLC
• params (STRUCT)
• DIAL: T_String phone
• SERIAL:
• ETHERNET:
• DIAL*TCP:T_String phone, T_String user, T* String pwd
• EPACK:T_String phone
• WIRELESS*GPRS: T_Byte cid, T_String apn, T_String user, T* String pwd
• RENPAC: T*String phone, T_String user, T* String pwd
• WIRELESS_CSD: T_String phone, T_String user, T_String pwd
• WIRELESS_WIFI: T_String ssid, T_Byte channel, T_String key
• RENPAC_PPP: T_String phone, T_String user, T_String pwd
• SDLC: T_String phone

16 - server (STRUCT)

• id (WORD)
• type (BYTE)
• params (STRUCT)
• Direct:
• Socket: T_Byte protocol(0 - TCP/IP, 1 - UDP), T_String ip, T_Word port
• Renpac: T_String host
• SDLC:

Eventos

20 - dial

Evento de marcar; dependiendo del tipo de evento informa el comienzo o el final de la ocurrencia.

21 - connect

Evento de establecimiento de conexión; dependiendo del tipo de evento informa el comienzo o el final de la ocurrencia.

22 - channel

Evento de establecimiento del canal de comunicación; aquí puede estar el PhSec; dependiendo del tipo de evento informa el comienzo o el final de la ocurrencia.

23 - send(WORD)

Evento de enviando datos, también puede informar la cantidad de bytes enviados usando el canal; dependiendo del tipo de evento informa el comienzo o el final de la ocurrencia.

24 - receive(WORD)

Evento de recepción de datos, también puede informar la cantidad de bytes recibidos usando el canal; dependiendo del tipo de evento informa el comienzo o el final de la ocurrencia.

25 - disconnect

Evento de desconexión; dependiendo del tipo de evento informa el comienzo o el final de la ocurrencia.

26 - waitConnection

Operación

30 - request

Evento de montaje de solicitud de operación (inicialización, transacción, etcétera.); dependiendo del tipo de evento informa el comienzo o el final de la ocurrencia.

31 - response

Evento de procesamiento de respuesta de operación (inicialización, transacción, etcétera.); dependiendo del tipo de evento informa el comienzo o el final de la ocurrencia.

32 - confirmation

Evento de montaje de confirmación; dependiendo del tipo de evento informa el comienzo o el final de la ocurrencia.

Información de inicialización

40 - params(STRUCT)

• forceFull (BOOLEAN)
• receiveData (BOOLEAN)

Información de la transacción:

55 - params(STRUCT)

• providerId (WORD)
• transactionId (WORD)

56 - capture

57 - print(STRUCT)

• via (BYTE)
• linhas (BYTE)

58 - conclusion

Consultas de Base de Datos

Lista de Transacciones Aprobadas (Rendimiento)

    select
      a.site as ESTABELECIMENTO, a.client as POS, a.NSU, to_char(a.timestamp, 'DD/MM HH24:MI:SS') as HORARIO,
      case BITAND(status.battery, 128)
        when 128 then 'SIM'
        else 'NAO' end as CARREGANDO,
      case BITAND(status.battery, 64)
        when 128 then 'SIM'
        else 'NAO' end as FONTE,
      case BITAND(status.battery, 63)
        when 1 then 'MUITO BAIXO'
        when 2 then 'BAIXO'
        when 3 then 'MEIA'
        when 4 then 'ALTA'
        when 5 then 'CHEIA'
        else '---'
        END as BATERIA,
      status.rssi as SINAL,
      lpad(sum(enviado) || ' bytes', 12, '0') as ENVIADO,
      lpad(sum(recebido) || ' bytes', 12, '0') as RECEBIDO,
      lpad((sum(inicio_envio) - sum(fim_captura)) * 10, 5, '0') || ' ms' as REQUISIÇÃO,
      lpad((sum(inicio_recepcao) - sum(inicio_envio)) * 10, 5, '0') || ' ms' as ENVIO,
      lpad((sum(fim_recepcao) - sum(inicio_recepcao)) * 10, 5, '0') || ' ms' as RECEPCAO,
      lpad((sum(fim) - sum(fim_recepcao)) * 10, 5, '0') || ' ms' as RESPOSTA,
      --sum(inicio) as inicio, sum(fim) as fim,
      lpad((sum(fim) - sum(fim_captura)) * 10, 5, '0') || ' ms' as TOTAL
    from
    (
      select distinct
        site.specific_id as site, client.specific_id as client, statistic.nsu, statistic.timestamp, max(info.ticks) as fim_captura, 0 as inicio_envio, 0 as inicio_recepcao, 0 as fim_recepcao, 0 as fim, 0 as enviado, 0 as recebido
      from
        phs_statistic statistic
      join
        dmn_client client on statistic.terminal_id = client.client_id
      join
        dmn_terminal terminal on client.client_id = terminal.client_id
      join
        dmn_site site on terminal.site_id = site.site_id
      join
        phs_statistic_info info on info.statistic_id = statistic.statistic_id
      where
        statistic.result = 0 and statistic.usage = 2
        and info.specific_id = '56' and info.type = 1
      group by
        site.specific_id, client.specific_id, statistic.nsu, statistic.timestamp

      union all

      select distinct
        site.specific_id as site, client.specific_id as client, statistic.nsu, statistic.timestamp, 0 as fim_captura, max(info.ticks) as inicio_envio, 0 as inicio_recepcao, 0 as fim_recepcao, 0 as fim, 0 as enviado, 0 as recebido
      from
        phs_statistic statistic
      join
        dmn_client client on statistic.terminal_id = client.client_id
      join
        dmn_terminal terminal on client.client_id = terminal.client_id
      join
        dmn_site site on terminal.site_id = site.site_id
      join
        phs_statistic_info info on info.statistic_id = statistic.statistic_id
      where
        statistic.result = 0 and statistic.usage = 2
        and info.specific_id = '23' and info.type = 0
      group by
        site.specific_id, client.specific_id, statistic.nsu, statistic.timestamp

      union all

      select distinct
        site.specific_id as site, client.specific_id as client, statistic.nsu, statistic.timestamp, 0 as fim_captura, 0 as inicio_envio, max(info.ticks) as inicio_recepcao, 0 as fim_recepcao, 0 as fim, 0 as enviado, 0 as recebido
      from
        phs_statistic statistic
      join
        dmn_client client on statistic.terminal_id = client.client_id
      join
        dmn_terminal terminal on client.client_id = terminal.client_id
      join
        dmn_site site on terminal.site_id = site.site_id
      join
        phs_statistic_info info on info.statistic_id = statistic.statistic_id
      where
        statistic.result = 0 and statistic.usage = 2
        and info.specific_id = '23' and info.type = 1
      group by
        site.specific_id, client.specific_id, statistic.nsu, statistic.timestamp

      union all

      select distinct
        site.specific_id as site, client.specific_id as client, statistic.nsu, statistic.timestamp, 0 as fim_captura, 0 as inicio_envio, 0 as inicio_recepcao, max(info.ticks) as fim_recepcao, 0 as fim, 0 as enviado, 0 as recebido
      from
        phs_statistic statistic
      join
        dmn_client client on statistic.terminal_id = client.client_id
      join
        dmn_terminal terminal on client.client_id = terminal.client_id
      join
        dmn_site site on terminal.site_id = site.site_id
      join
        phs_statistic_info info on info.statistic_id = statistic.statistic_id
      where
        statistic.result = 0 and statistic.usage = 2
        and info.specific_id = '24' and info.type = 1
      group by
        site.specific_id, client.specific_id, statistic.nsu, statistic.timestamp

      union all

      select distinct
        site.specific_id as site, client.specific_id as client, statistic.nsu, statistic.timestamp, 0 as fim_captura, 0 as inicio_envio, 0 as inicio_recepcao, 0 as fim_recepcao, min(info.ticks) as fim, 0 as enviado, 0 as recebido
      from
        phs_statistic statistic
      join
        dmn_client client on statistic.terminal_id = client.client_id
      join
        dmn_terminal terminal on client.client_id = terminal.client_id
      join
        dmn_site site on terminal.site_id = site.site_id
      join
        phs_statistic_info info on info.statistic_id = statistic.statistic_id
      where
        statistic.result = 0 and statistic.usage = 2
        and info.specific_id = '57' and info.type = 0
      group by
        site.specific_id, client.specific_id, statistic.nsu, statistic.timestamp

      union all

        select distinct
          site.specific_id as site, client.specific_id as client, statistic.nsu, statistic.timestamp, 0 as fim_captura, 0 as inicio_envio, 0 as inicio_recepcao, 0 as fim_recepcao, 0 as fim, p8.size_sent as enviado, p9.size_received as recebido
        from
          phs_statistic statistic
        join
          dmn_client client on statistic.terminal_id = client.client_id
        join
          dmn_terminal terminal on client.client_id = terminal.client_id
        join
          dmn_site site on terminal.site_id = site.site_id
        join
          phs_statistic_info info on info.statistic_id = statistic.statistic_id
        left join PHS_STAT_COMM_INF_DET        p3  on  p3.STAT_INF_DET_ID  = info.STATISTIC_INFO_DETAIL_ID
        left join PHS_STAT_OPER_INF_DET        p4  on  p4.STAT_INF_DET_ID  = info.STATISTIC_INFO_DETAIL_ID
        left join PHS_STAT_STATUS_INF_DET      p5  on  p5.STAT_INF_DET_ID  = info.STATISTIC_INFO_DETAIL_ID
        left join PHS_STAT_CONN_INF_DET        p6  on  p6.STAT_INF_DET_ID  = info.STATISTIC_INFO_DETAIL_ID
        left join PHS_STAT_SRV_INF_DET         p7  on  p7.STAT_INF_DET_ID  = info.STATISTIC_INFO_DETAIL_ID
        left join PHS_STAT_SEND_INF_DET        p8  on  p8.STAT_INF_DET_ID  = info.STATISTIC_INFO_DETAIL_ID
        left join PHS_STAT_RECEI_INF_DET       p9  on  p9.STAT_INF_DET_ID  = info.STATISTIC_INFO_DETAIL_ID
        left join PHS_STAT_INIT_PARAM_INF_DET p10  on p10.STAT_INF_DET_ID  = info.STATISTIC_INFO_DETAIL_ID
        left join PHS_STAT_TRS_PRINT_INF_DET  p11  on p11.STAT_INF_DET_ID  = info.STATISTIC_INFO_DETAIL_ID
        left join PHS_STAT_TRS_PRM_INF_DET    p12  on p12.STAT_INF_DET_ID  = info.STATISTIC_INFO_DETAIL_ID
        left join PHS_STAT_INF_DET_CFG_PARAMS p13  on p13.PARAMS_ID        = p6.PARAMS_ID or p13.params_id = p7.params_id
        where
          statistic.result = 0 and statistic.usage = 2
          and info.specific_id in ('23', '24') and info.type = 1

    ) a
    join
      phs_statistic statistic on statistic.nsu = a.nsu
    join
      phs_statistic_info statistic_info on statistic.statistic_id = statistic_info.statistic_id
    join
      phs_stat_status_inf_det status on statistic_info.statistic_info_detail_id = status.stat_inf_det_id
    where
      a.timestamp > to_date('01/10/2016', 'DD/MM/YYYY')
      --and site = '006000006599002' and client = '00000005'
      --and a.nsu > 1473094434
    --having (sum(fim) - sum(inicio)) * 10 > 0
    group by a.site, a.client, a.nsu, a.TIMESTAMP, status.battery, status.rssi, status.ber
    order by a.site, a.client, a.TIMESTAMP desc
    ;

Vista Detallada de la Última Transacción

    select
      TO_CHAR(ps.timestamp, 'DD/MM/YY hh24:mi:ss') as timestamp,
      CASE ps.usage
        WHEN 0 THEN 'TELECARGA'
        WHEN 1 THEN 'INICIALIZACAO'
        WHEN 2 THEN 'TRANSACAO'
        WHEN 3 THEN 'KEEPALIVE'
      END as USAGE,
      psi.ticks * 10 as ticks,
      case ps.reason
        when 0 then 'NAO IDENTIFICADO'
        when 1 then 'OPERADOR'
        when 2 then 'EVENTO'
        when 3 then 'INTEGRACAO'
        when 4 then 'SERVIDOR'
        when 5 then 'ATUALIZACAO'
      end as reason,
      case ps.result
        when 0 then 'APROVADO_ONLINE'
        when 1 then 'APROVADO_OFFLINE'
        when 2 then 'NEGADO_ONLINE'
        when 3 then 'NEGADO_OFFLINE'
        when 4 then 'ERRO_COMM'
        when 5 then 'CANCELADO'
      end as result,
      CASE psi.specific_id
        WHEN   0 THEN '000 - reason'
        WHEN   1 THEN '001 - result'
        WHEN   5 THEN '005 - comm'
        WHEN   6 THEN '006 - operation'
        WHEN  10 THEN '010 - status'
        WHEN  15 THEN '015 - conn'
        WHEN  16 THEN '016 - server'
        WHEN  20 THEN '020 - dial'
        WHEN  21 THEN '021 - connect'
        WHEN  22 THEN '022 - channel'
        WHEN  23 THEN '023 - send'
        WHEN  24 THEN '024 - receive'
        WHEN  25 THEN '025 - disconnect'
        WHEN  26 THEN '026 - waitConnection'
        WHEN  30 THEN '030 - request'
        WHEN  31 THEN '031 - response'
        WHEN  32 THEN '032 - confirmation'
        WHEN  40 THEN '040 - params'
        WHEN  55 THEN '055 - params'
        WHEN  56 THEN '056 - capture'
        WHEN  57 THEN '057 - print'
        WHEN  58 THEN '058 - conclusion'
        WHEN 100 THEN '100 - CieloError'
        WHEN 101 THEN '101 - CieloProduct'
      END as INFO,
      psi.type,
      --' ' || p1.reason as reason,
      --' ' || p2.result as result_,
      ' ' || p3.code as code,
      ' ' || p3.detail as detail,
      ' ' || p3.native_error as native_error,
      ' ' || p4.code as code,
      ' ' || p4.message as message,
      ' ' || p5.battery as battery,
      ' ' || p5.rssi as rssi,
      ' ' || p5.ber as ber,
      ' ' || UTL_RAW.CAST_TO_RAW(UTL_RAW.CAST_TO_VARCHAR2(p5.geo)) as geo,
      ' ' || p6.specific_id as conn_id,
      ' ' || p7.p_type as conn_type,
      ' ' || p8.size_sent as size_sent,
      ' ' || p9.size_received as size_received,
      ' ' || p10.force_full as force_full,
      ' ' || p10.receive_data as recevice_data,
      ' ' || p11.printed_lines as printed_lines,
      ' ' || p12.provider_id as provider_id,
      ' ' || p12.transaction_id as transaction_id,
      ' ' || p13.phone as phone,
      ' ' || p13.p_user as p_user,
      ' ' || p13.p_pwd as pwd,
      ' ' || p13.p_host as host,
      ' ' || p13.protocol as protocol,
      TRUNC(p13.p_ip / 16777216) || ' ' || MOD(TRUNC(p13.p_ip / 65536), 256) || ' ' || MOD(TRUNC(p13.p_ip / 256), 256) || ' ' || MOD(p13.p_ip, 256) as ip,
      ' ' || p13.p_port as port,
      ' ' || p13.p_cid as cid,
      ' ' || p13.p_apn as apn,
      ' ' || p13.ssid as ssid,
      ' ' || p13.channel as channel,
      ' ' || p13.p_key as p_key
    from
      (
        select statistic_id, timestamp, usage, reason, result from (
          select
            statistic.statistic_id, statistic.timestamp, statistic.usage, statistic.reason, statistic.result
          from
            phs_statistic statistic
          join
            dmn_client client on statistic.terminal_id = client.client_id
          join
            phs_statistic_info info on info.statistic_id = statistic.statistic_id
          --where
            --client.specific_id = '00000005'
          order by
            statistic.timestamp desc, info.ticks
        ) where rownum <= 1
      ) ps
    left join phs_statistic_info psi on psi.statistic_id = ps.statistic_id
    left join PHS_STAT_COMM_INF_DET        p3  on  p3.STAT_INF_DET_ID  = psi.STATISTIC_INFO_DETAIL_ID
    left join PHS_STAT_OPER_INF_DET        p4  on  p4.STAT_INF_DET_ID  = psi.STATISTIC_INFO_DETAIL_ID
    left join PHS_STAT_STATUS_INF_DET      p5  on  p5.STAT_INF_DET_ID  = psi.STATISTIC_INFO_DETAIL_ID
    left join PHS_STAT_CONN_INF_DET        p6  on  p6.STAT_INF_DET_ID  = psi.STATISTIC_INFO_DETAIL_ID
    left join PHS_STAT_SRV_INF_DET         p7  on  p7.STAT_INF_DET_ID  = psi.STATISTIC_INFO_DETAIL_ID
    left join PHS_STAT_SEND_INF_DET        p8  on  p8.STAT_INF_DET_ID  = psi.STATISTIC_INFO_DETAIL_ID
    left join PHS_STAT_RECEI_INF_DET       p9  on  p9.STAT_INF_DET_ID  = psi.STATISTIC_INFO_DETAIL_ID
    left join PHS_STAT_INIT_PARAM_INF_DET p10  on p10.STAT_INF_DET_ID  = psi.STATISTIC_INFO_DETAIL_ID
    left join PHS_STAT_TRS_PRINT_INF_DET  p11  on p11.STAT_INF_DET_ID  = psi.STATISTIC_INFO_DETAIL_ID
    left join PHS_STAT_TRS_PRM_INF_DET    p12  on p12.STAT_INF_DET_ID  = psi.STATISTIC_INFO_DETAIL_ID
    left join PHS_STAT_INF_DET_CFG_PARAMS p13  on p13.PARAMS_ID        = p6.PARAMS_ID or p13.params_id = p7.params_id
    order by
      psi.ticks
    ;